package com.jlc.book.shop.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.jlc.book.shop.dao.BookDAO;
import com.jlc.book.shop.to.BookTO;
import com.jlc.book.shop.util.JDBCUtil;

public class JDBCBookDAO implements BookDAO {
	
	
	Logger log=Logger.getLogger(this.getClass());

	@Override
	public boolean alreadyExist(BookTO bto) {
		
		boolean exist=false;
		Connection con=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		
		try
		{
			con=JDBCUtil.getConnection();
			ps=con.prepareStatement("select * from book_table where bookName=? and author=? and cost=? and publication=? and edition=?");
			ps.setString(1, bto.getBookName());
			ps.setString(2, bto.getAuthor());
			ps.setFloat(3, bto.getCost());
			ps.setString(4, bto.getPublication());
			ps.setString(5, bto.getEdition());
			rs=ps.executeQuery();
			
			if(rs.next())
			{
				exist=true;
			}
			
		}
		catch (Exception e) {
			
			exist=false;
			log.error("Exception in alreay Exist:in",e);
		}
		
		finally
		{
			JDBCUtil.cleanup(con, ps, rs);
		}
		
		
		return exist;
	}

	@Override
	public boolean addBook(BookTO bto) {
		
		boolean added=false;
		Connection con=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		
		try
		{
			con=JDBCUtil.getConnection();
			ps=con.prepareStatement("insert into book_table(bookName,author,cost,publication,edition) values(?,?,?,?,?) ");
			ps.setString(1, bto.getBookName());
			ps.setString(2, bto.getAuthor());
			ps.setFloat(3, bto.getCost());
			ps.setString(4, bto.getPublication());
			ps.setString(5, bto.getEdition());
			
			int x=ps.executeUpdate();
			
			if(x>0)
			{
				added=true;
			}
			
		}
		catch (Exception e) {
			
			added=false;
			log.error("Exception in addBook:in",e);
		}
		
		finally
		{
			JDBCUtil.cleanup(con, ps, rs);
		}
		
		
		return added;
	}

	@Override
	public int getTotalNumberOfBook(BookTO bto)
	{
		int total=0;
		Connection con=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		
		try
		{
			con=JDBCUtil.getConnection();
			ps=con.prepareStatement(prepareQuery(bto));
			int i=1;
			if(bto.getBookName()!=null && bto.getBookName().length()!=0)
			
				ps.setString(i++, "%"+bto.getBookName().trim()+"%");
			System.out.println(bto.getBookName());
		
			if(bto.getCost()!=0)
		    ps.setFloat(i++, bto.getCost());
			System.out.println(bto.getCost());
			
			if(bto.getAuthor()!=null && bto.getAuthor().length()!=0)
				ps.setString(i++, "%"+bto.getAuthor().trim()+"%");
			
			System.out.println(bto.getAuthor());
			
			if(bto.getPublication()!=null && bto.getPublication().length()!=0)
				
			ps.setString(i++, "%"+bto.getPublication().trim()+"%");
			System.out.println(bto.getPublication());
				
			if(bto.getEdition()!=null && bto.getEdition().length()!=0)
			
				ps.setString(i++,bto.getEdition().trim());
				
			
			System.out.println(bto.getEdition());
			
			
			rs=ps.executeQuery();
			while(rs.next())
			{
				total++;
			}
			System.out.println("anil"+total);
		}
		catch (Exception e) {
		
			log.error("Exception in getTotalNumberOfBook",e);
		}
		
		finally
		{
			JDBCUtil.cleanup(con, ps, rs);
		}
		return total;
		
		
	}

	private String prepareQuery(BookTO bto) {
		
		String qry="select * from book_table";
		boolean whereAdded=false;
		if(bto.getBookName()!=null && bto.getBookName().length()!=0)
		{
			qry+=" where bookName like ?";
			whereAdded=true;
		}
		if(bto.getCost()!=0)
		{
			if(whereAdded)
			{
				qry+=" and cost=?";
				whereAdded=true;
			}
			else
			{
				qry+=" where cost=?";
				whereAdded=true;
			}
		}
		
		if(bto.getAuthor()!=null && bto.getAuthor().length()!=0)
		{
			if(whereAdded)
			{
			qry+=" and author like ?";
		}
			else
			{
				qry+=" where author like ?";
				whereAdded=true;
			}
		}
		

		if(bto.getPublication()!=null && bto.getPublication().length()!=0)
		{
			if(whereAdded)
			{
			qry+=" and publication like ?";
		}
			else
			{
				qry+=" where publication like ?";
				whereAdded=true;
			}
		}
		

		if(bto.getEdition()!=null && bto.getEdition().length()!=0)
		{
			if(whereAdded)
			{
			qry+=" and edition=?";
		}
			else
			{
				qry+=" where edition=?";
				whereAdded=true;
			}
		}
		
		System.out.println(qry);
		return qry;
	}

	@Override
	public List searchBook(BookTO bto, int start, int noBook) {
		List bookList=null;
		Connection con=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		
		try
		{
			con=JDBCUtil.getConnection();
ps=con.prepareStatement(prepareQuery(bto),ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
			
			
			int i=1;
			if(bto.getBookName()!=null && bto.getBookName().length()!=0)
			
				ps.setString(i++,"%"+bto.getBookName().trim()+"%");
				
			if(bto.getCost()!=0)
		    ps.setFloat(i++, bto.getCost());
			
			if(bto.getAuthor()!=null && bto.getAuthor().length()!=0)
			
				ps.setString(i++, "%"+bto.getAuthor().trim()+"%");
				
			if(bto.getPublication()!=null && bto.getPublication().length()!=0)
			
				ps.setString(i++, "%" +bto.getPublication().trim()+"%");
				
			if(bto.getEdition()!=null && bto.getEdition().length()!=0)
			
				ps.setString(i++,bto.getEdition().trim());
				
			rs=ps.executeQuery();
			rs.absolute(start+1);
			int x=1;
			bookList = new ArrayList();
			
			do
			{
BookTO bookTO=new  BookTO(rs.getString(2), rs.getString(3), rs.getString(5),rs.getString(6),rs.getFloat(4));

				bookTO.setBookId(rs.getInt(1));
				bookList.add(bookTO);
				x++;
				
			}while(rs.next() && x<=noBook);
			
		}
		catch (Exception e) {
			bookList=null;
			log.error("Exception in searchBook:\n",e);
			
		}
		
		finally
		{
			JDBCUtil.cleanup(con, ps, rs);
		}
		
		return bookList;
	}

	@Override
	public boolean deleteBook(int bookId) {
		
		log.info(bookId+" ");
		boolean deleted=false;
		Connection con=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		
		try
		{
			con=JDBCUtil.getConnection();
			ps=con.prepareStatement("delete from book_table where bookId=?");
			ps.setInt(1, bookId);
			int x=ps.executeUpdate();
			
			if(x>0)
			{
				deleted=true;
			}
			
		}
		
		catch (Exception e) {
			deleted=false;
			log.error("Exception in deleteBook:\n",e);
			
		}
		
		finally
		{
			JDBCUtil.cleanup(con, ps, rs);
		}
		
		return deleted;
	}

	@Override
	public BookTO getBookById(String bid) {
		
		BookTO bto=null;
		Connection con=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		
		try
		{
			con=JDBCUtil.getConnection();
			ps=con.prepareStatement("select * from book_table where bookId=?");
			ps.setInt(1, Integer.parseInt(bid));
			rs=ps.executeQuery();
			
			if(rs.next())
			{
				bto=new BookTO();
				bto.setBookId(rs.getInt(1));
				bto.setBookName(rs.getString(2));
				bto.setAuthor(rs.getString(3));
				bto.setCost(rs.getFloat("cost"));
				bto.setPublication(rs.getString("publication"));
				bto.setEdition(rs.getString("edition"));
			}
		}
		catch (Exception e) 
		{
			bto=null;;
			log.error("Exception in getBookById:\n",e);
			
			
		}
		
		finally
		{
			JDBCUtil.cleanup(con, ps, rs);
			
		}
		
		return bto;
	}
	
}
